-- =============================================
-- Author:		<kmb>
-- Create date: <08/06/2014>
-- Description:	<Remittance Cash Received Serial>
-- =============================================
DROP PROCEDURE sp_rmt_cash_received_serial;
GO
CREATE PROCEDURE [dbo].[sp_rmt_cash_received_serial]
@param1 bigint,
@param2 date
AS
BEGIN
declare @serial int;
if not exists (select Serial from CashReceivedSerial where BranchId = @param1 and RMTDate = @param2)
	begin
		set @serial = 1;
		insert into CashReceivedSerial(BranchId, RMTDate, Serial) values(@param1, @param2, @serial);
	end
else
	begin
		select @serial = Serial + 1 from CashReceivedSerial where BranchId = @param1 and RMTDate = @param2;
		update CashReceivedSerial set Serial = @serial where BranchId = @param1 and RMTDate = @param2;
	end
declare @branchCode varchar(20);
set @branchCode = (select BranchCode from Branch where BranchId = @param1);
select @branchCode + 'RM' + convert(VARCHAR, year(getdate()) % 100)
 + '/' + dbo.[FN_LPAD_LEADING_ZERO] (5,@serial)
END

GO